
--drop assembly objects (links)
DECLARE @sql_error INT
        , @nsql NVARCHAR(MAX)
        , @xml_schema_collection VARCHAR(512)

SELECT @xml_schema_collection = 'ssf.object_header'
    , @sql_error = NULL
    , @nsql = ''

SELECT @nsql = @nsql
	+ 'DROP ' + CASE WHEN o.type_desc LIKE '%FUNCTION'
		THEN 'FUNCTION'
		WHEN o.type_desc LIKE '%TRIGGER'
		THEN 'TRIGGER'
		WHEN o.type_desc LIKE '%PROCEDURE'
		THEN 'PROCEDURE'
		WHEN o.type_desc = 'USER_TABLE'
		THEN 'TABLE'
		WHEN o.type_desc = 'VIEW'
		THEN 'VIEW'
		END
	+ ' '
	+ ISNULL(QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.', '')
	+ QUOTENAME(o.[name])
	+ CHAR(13) + CHAR(10)
FROM sys.dm_sql_referencing_entities(@xml_schema_collection, 'XML_SCHEMA_COLLECTION') r
JOIN sys.objects o
ON r.referencing_entity_name = o.[name]
AND r.referencing_schema_name = SCHEMA_NAME(o.[schema_id])

--PRINT(@nsql)

EXECUTE(@nsql)

IF @@ERROR <> 0
BEGIN
	RAISERROR('Failed to drop xml schema collection referencing objects.', 16, 1)
END
